import openpyxl
from exts import db
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.associationproxy import association_proxy
from collections import OrderedDict


class Teacher(db.Model):
    __tablename__ = "teacher"
    ID = db.Column(db.Integer, primary_key=True)
    password = db.Column(db.String(255), nullable=False)
    name = db.Column(db.String(255), nullable=False)
    email = db.Column(db.String(255), nullable=False)

    def to_json(self):
        return OrderedDict(
            ID=self.ID,
            password=self.password,
            name=self.name,
            class_id=self.class_id,
            email=self.email,
        )


class Student(db.Model):
    __tablename__ = "student"
    ID = db.Column(db.BigInteger, primary_key=True)
    password = db.Column(db.String(255), nullable=False)
    name = db.Column(db.String(255), nullable=False)
    sex = db.Column(db.String(10), nullable=True)
    email = db.Column(db.String(255), nullable=True)
    class_id1 = db.Column(db.String(255), nullable=True)
    class_id2 = db.Column(db.Integer(), nullable=True)
    information = db.Column(db.String(255))

    def to_json(self):
        return OrderedDict(
            ID=self.ID,
            password=self.password,
            name=self.name,
            sex=self.sex,
            email=self.email,
            class_id1=self.class_id1,
            class_id2=self.class_id2,
            information=self.information
        )


class Homework(db.Model):
    __tablename__ = "homework"
    ID = db.Column(db.Integer, primary_key=True)
    tid = db.Column(db.Integer, db.ForeignKey("teacher.ID"), nullable=False)  # 外键
    # 自动给teacher一个属性，用来获取发布的作业列表
    # author = db.relationship("Teacher",backref="homeworks")
    title = db.Column(db.String(255), nullable=False)
    h_content = db.Column(db.String(255), nullable=False)
    homework_type = db.Column(db.String(50), nullable=False, default='code')  # 作业类型：'code' 或 'text'
    issuedate = db.Column(db.String(255), nullable=True)
    deadline = db.Column(db.String(255), nullable=False)
    submit_limit = db.Column(db.Integer)
    attachment_path = db.Column(db.String(255))
    attachment_name = db.Column(db.String(255))
    answer = db.Column(db.String(255))

    def to_json(self):
        return OrderedDict(
            ID=self.ID,
            tid=self.tid,
            title=self.title,
            h_content=self.h_content,
            homework_type=self.homework_type,
            issuedate=self.issuedate,
            deadline=self.deadline,
            submit_limit=self.submit_limit,
            attachment_path=self.attachment_path,
            attachment_name=self.attachment_name,
            answer=self.answer,
        )


class Uploadhomework(db.Model):
    __tablename__ = "uploadhomework"
    ID = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sid = db.Column(db.BigInteger, db.ForeignKey("student.ID", ondelete="CASCADE"), nullable=False)  # 外键
    student = db.relationship("Student", backref="upload_homeworks")
    hnumber = db.Column(db.Integer, db.ForeignKey("homework.ID", ondelete="CASCADE"), nullable=False)  # 外键
    frequency = db.Column(db.Integer, nullable=False)
    homeworkpath = db.Column(db.String(255))  # 代码文件路径
    text_content = db.Column(db.Text)  # 文本作业内容
    submission_type = db.Column(db.String(50), nullable=False, default='code')  # 提交类型：'code' 或 'text'
    htime = db.Column(db.String(255))
    grade = db.Column(db.Integer)
    homework = db.relationship("Homework", backref="upload_homeworks")

    def to_json(self):
        return OrderedDict(
            ID=self.ID,
            sid=self.sid,
            sname=self.student.name,
            sclass=self.student.class_id1,
            sclass2=self.student.class_id2,
            hnumber=self.hnumber,
            frequency=self.frequency,
            homeworkpath=self.homeworkpath,
            text_content=self.text_content,
            submission_type=self.submission_type,
            htime=self.htime,
            grade=self.grade if self.grade is not None else "等待评分",
        )

    def is_this_class(self, bigsclass):
        if int(bigsclass) == int(self.student.class_id2):
            return True
        return False
    
    def is_this_class1(self, bigsclass):
        if bigsclass == self.student.class_id1:
            return True
        return False

    def get_copy_result(self):
        homeworkpath = self.homework.attachment_path
        copy_file = homeworkpath + "/copy.xlsx"
        try:
            workbook = openpyxl.load_workbook(copy_file)
        # 选择一个工作表
            worksheet = workbook['Sheet1']  # 替换为你的工作表名称
        # 定义要查找的ID
            target_id = self.sid  # 替换为你要查找的ID
        # 检查单元格是否匹配目标ID
            for row in worksheet.iter_rows(values_only=True):
                for cell in row:
                    if str(cell) == str(target_id):
                        return "抄袭"
        # 关闭工作簿
            workbook.close()
        except Exception as e:    
            return "未开始检测"
        return "正常"


class Copy(db.Model):
    __tablename__ = "copy"
    hid = db.Column(db.Integer, db.ForeignKey("homework.ID", ondelete="CASCADE"), primary_key=True)
    # homework = db.relationship("Homework",backref="copy")
    sid = db.Column(db.String(255), primary_key=True)
    semblance = db.Column(db.String(255))

    def to_json(self):
        return OrderedDict(
            hid=self.hid,
            sid=self.sid,
            semblance=self.semblance + "%",
        )


class Copyversion(db.Model):
    __tablename__ = "copyversion"
    hid = db.Column(db.Integer(), primary_key=True)
    vsum = db.Column(db.Integer())  # 版本数目
    csum = db.Column(db.Integer())  # 抄袭人数
    class_id1 = db.Column(db.String(255), primary_key=True)
    class_id2 = db.Column(db.String(255), primary_key=True)

    def to_json(self):
        return OrderedDict(
            hid=self.hid,
            vsum=self.vsum,
            csum=self.csum,
            class_id1=self.class_id1,
            class_id2=self.class_id2,
        )


class Homepage(db.Model):
    __tablename__ = "homepage"
    ID = db.Column(db.Integer, primary_key=True)
    headline = db.Column(db.String(255), nullable=False)
    notice = db.Column(db.String(255))
    notice_time = db.Column(db.String(255))
    issuer = db.Column(db.String(255))

    def to_json(self):
        return OrderedDict(
            ID=self.ID,
            headline=self.headline,
            notice=self.notice,
            notice_time=self.notice_time,
            issuer=self.issuer,
        )


class PatrioticAnalysis(db.Model):
    """爱国情感分析结果模型"""
    __tablename__ = "patriotic_analysis"
    ID = db.Column(db.Integer, primary_key=True)
    upload_homework_id = db.Column(db.Integer, db.ForeignKey("uploadhomework.ID", ondelete="CASCADE"), nullable=False)
    upload_homework = db.relationship("Uploadhomework", backref="patriotic_analyses")
    
    # 分析结果
    patriotic_score = db.Column(db.Float, nullable=False)  # 爱国情感得分 0-1
    patriotic_level = db.Column(db.String(50), nullable=False)  # 爱国情感等级
    is_patriotic = db.Column(db.Boolean, nullable=False)  # 是否具有爱国情感
    patriotic_words_count = db.Column(db.Integer, default=0)  # 爱国相关词汇数量
    anti_patriotic_words_count = db.Column(db.Integer, default=0)  # 反爱国相关词汇数量
    confidence = db.Column(db.Float, default=0.0)  # 分析置信度
    patriotic_breakdown = db.Column(db.Text)  # 爱国情感详细分析
    
    # 评分相关
    grade = db.Column(db.Integer)  # 基于爱国情感的评分
    analysis_time = db.Column(db.DateTime, default=db.func.now())  # 分析时间
    
    def to_json(self):
        return OrderedDict(
            ID=self.ID,
            upload_homework_id=self.upload_homework_id,
            patriotic_score=self.patriotic_score,
            patriotic_level=self.patriotic_level,
            is_patriotic=self.is_patriotic,
            patriotic_words_count=self.patriotic_words_count,
            anti_patriotic_words_count=self.anti_patriotic_words_count,
            confidence=self.confidence,
            patriotic_breakdown=self.patriotic_breakdown,
            grade=self.grade,
            analysis_time=self.analysis_time.strftime('%Y-%m-%d %H:%M:%S') if self.analysis_time else None
        )
